package com.qingniao.hb.dao.impl;

import com.alibaba.druid.util.StringUtils;
import com.qingniao.hb.dao.BookDao;
import com.qingniao.hb.entity.Books;
import com.qingniao.hb.utitls.DruidUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;

/**
 * @author xlzhang
 * @date 2023/4/18 10:58
 */
@Slf4j
public class BookDaoImpl implements BookDao {
    // 所以在创建QueryRunner对象时，传递一个数据库连接池对象(数据源)给他
    private final QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    @Override
    public List<Books> selectPageByParams(HashMap<String, Object> paramsMap) throws SQLException {
        // 拼接sql语句
        // select * from hb_books where title like '%西%' and price <= 45.6 limit 0,5
        // 1=1 是为了解决没有参数时的条件问题
        String sql = "select * from hb_books where 1=1";
        // 先创建一个有循环的集合
        final ArrayList<Object> args = new ArrayList<>();
        // 调用公共的sql拼接方法拼接sql
        sql = joinSql(paramsMap, sql, args);
        // 处理分页相关
        sql = sql + " limit ?,?";
        // 根据当前页码计算分页的起始条数
        final int page = (int) paramsMap.get("page");  // 3
        // 获取每页显示的条数
        final int limit = (int) paramsMap.get("limit");

        // 计算公式  (page - 1) * limit
        int startNum = (page - 1) * limit;
        // 将分页的参数保存到集合中,注意添加时的顺序
        args.add(startNum);
        args.add(limit);

        // 输出当前拼接后的sql和参数集合
        log.debug("--sql: {}",sql);
        log.debug("--args: {}",args);

        return qr.query(sql, new BeanListHandler<>(Books.class),args.toArray());
    }

    @Override
    public int insertBook(Books books) throws SQLException {
        String sql = "insert into hb_books(title, author, price, sales, stock, img_path) VALUES(?,?,?,?,?,?)";
        return qr.update(sql,books.getTitle(),
                            books.getAuthor(),
                            books.getPrice(),
                            books.getSales(),
                            books.getStock(),
                            books.getImg_path());
    }

    @Override
    public int updateBook(Books books) throws SQLException {
        String sql = "update hb_books set title=?,author=?,price=?,stock=?";
        // 创建一个列表集合保存参数
        final ArrayList<Object> values = new ArrayList<>();
        // 注意这里的顺序要跟sql中的 ? 号一致
        Collections.addAll(values,books.getTitle(),books.getAuthor(),books.getPrice(),books.getStock());
        // 如果图片字段有值，表示用户修改了图片
        if (!StringUtils.isEmpty(books.getImg_path())){
            sql = sql + ",img_path=? ";
            values.add(books.getImg_path());
        }
        // 处理条件
        sql = sql + " where id=?";
        values.add(books.getId());

        return qr.update(sql,values.toArray());
    }

    @Override
    public int deleteBook(String bid) throws SQLException {
        String sql = "delete from hb_books where id=?";
        return qr.update(sql,bid);
    }

    @Override
    public int deleteBatch(Object[] bids) throws SQLException {
        String sql = "delete from hb_books where id in(";
        // 变量当前参数
        for (Object bid : bids) {
            sql = sql + "?,";
        }
        sql = sql + "null)";
        return qr.update(sql,bids);
    }

    @Override
    public Long selectCountByParams(HashMap<String, Object> paramsMap) throws SQLException {
        String sql = "select count(*) from hb_books where 1=1";
        // 先创建一个有循环的集合
        final ArrayList<Object> args = new ArrayList<>();
        // 调用公共的sql拼接方法拼接sql
        sql = joinSql(paramsMap, sql, args);
        // 执行查询
        return qr.query(sql, new ScalarHandler<>(), args.toArray());
    }

    private String joinSql(HashMap<String, Object> paramsMap, String sql, ArrayList<Object> args) {
        // 创建一个可变字符串，处理sql拼接
        final StringBuilder sbSql = new StringBuilder(sql);

        for (String key : paramsMap.keySet()) {
            switch (key) {
                case "title":
                case "author":
                    //sql = sql + " and title like ?";
                    sbSql.append(" and ").append(key).append(" like ?");
                    // 处理参数，需要给参数两边添加 %
                    final Object value = paramsMap.get(key);
                    // 同时添加参数到集合
                    args.add("%" + value + "%");
                    break;
                case "price":
                case "stock":
                    // 匹配小于等于规则
                    sbSql.append(" and ").append(key).append(" <= ?");
                    args.add(paramsMap.get(key));
                    break;
                case "sales":
                    // 匹配小于等于规则
                    sbSql.append(" and sales >= ?");
                    args.add(paramsMap.get(key));
                    break;
                default:
                    break;
            }
        }
        return sbSql.toString();
    }


}
